<?php
/**
 * This is a Zend_Db Select Select Class
 * It returns the primary data needed to rate a particular course
 */

class Community_Model_DbTable_coursedetails  extends Zend_Db_Table_Abstract {

	
	protected $_name = "course";
	protected $_db;	
	public function __construct(){
		
		$this->_db = Zend_Db_Table_Abstract::getDefaultAdapter();
	}
/**
 * SELECT  distinct school.name, dept.name from 
 * school, dept, school_dept where 
 *  school.id = school_dept.school_id 
 *  and dept.id = school_dept.dept_id;
 */
  	public function getAllSchooldepts() {
  	$select = new Zend_Db_Select($this->_db);
  	$res = $select->from(array('s'=>'school'), array('sid'=>'id','name'))
		  		   ->join(array('sd'=>'school_dept'), 's.id = sd.school_id', array('id'))
		  		   ->join(array('d'=>'dept'),'d.id = sd.dept_id', array('dname'=>'name'))
		  		   ->order('s.name');
		  		   
  //	$res = $select->__toString();
  //	Zend_Debug::dump($res);
  	return $res;
	}
	
	
/*
SELECT  distinct  school.name from
school, dept, school_dept where
school.id = school_dept.school_id
and dept.id = school_dept.dept_id;
*/
	public function getDistinctSchools() {
		
		$select = new Zend_Db_Select($this->_db);
		$res = $select->from(array('s'=>'school'), array('id','name'))
		  		   ->join(array('sd'=>'school_dept'), 's.id = sd.school_id', array(''))
		  		   ->join(array('d'=>'dept'),'d.id = sd.dept_id', array(''))
		  		   ->group('s.name')
		  		   ->order('s.name');
		  		   
  	//$res = $select->__toString();	
  //	Zend_Debug::dump($res);
  	return $res;
	}
	
/**
 * Fucntion: deptsOfSchool
 * This function returns all the departments that belong to a particular
 * school
 * SQL: QUERY
 * SELECT d.name as deptname FROM school_dept sd, dept d
 * where sd.school_id = $id and sd.dept_id = d.id;
 * @param school $id
 */
public function getschooldepts($id)	{
	
	$select = new Zend_Db_Select($this->_db);
	$res = $select->from(array('sd'=>'school_dept'), array(''))
				  ->where('sd.school_id = '.$id)
				  ->join(array('d'=>'dept'), 'sd.dept_id = d.id', array('id','deptname'=>'d.name'))
				  ->order('deptname');
				  
	return $res;
}

public function getcourses($id, $sid) {
	
	$select = new Zend_Db_Select($this->_db);
	$res = $select->from(array('c'=>'course'), array('id', 'name', 'course_no', 'school_id', 'dept_id'))
				  ->where('c.dept_id = '.$id)
				  ->where('c.school_id= '.$sid)
				  ->order('course_no');
				  
	return $res;
	
}


}//Closes the Class